<?php
// Tim'Roster
// Copyright (c) 2011 Nicolas Nallet <aspgic2@gmail.com>
//
// This file is part of Tim'Roster.
//
// Tim'Roster is free software; you can redistribute it and/or modify
// it under the terms of the GNU Affero General Public License as published by
// the Free Software Foundation; either version 3 of the License, or
// (at your option) any later version.
//
// Tim'Roster is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU Affero General Public License for more details.
//
// You should have received a copy of the GNU Affero General Public License
// along with this program; if not, write to the Free Software Foundation,
// Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301  USA

$xml_config = simplexml_load_file('config.xml');

include_once($_SERVER["DOCUMENT_ROOT"].$xml_config->roster_root_directory.'/data_access/base_DA.php');
include_once($_SERVER["DOCUMENT_ROOT"].$xml_config->roster_root_directory.'/class/Crafted_Item.php');
include_once($_SERVER["DOCUMENT_ROOT"].$xml_config->roster_root_directory.'/class/Material_Item.php');
include_once($_SERVER["DOCUMENT_ROOT"].$xml_config->roster_root_directory.'/class/Guild_Hall_Room.php');


class item_transaction_DA extends base_DA {

    /********** Transaction List Fields Name ************/
    const FL_TRANS_LIST_ID = 'id';
    const FL_TRANS_LIST_DATE = 'transaction_date';
    const FL_TRANS_LIST_GUILD_ID = 'guild_id';

    /********** Material Item Fields Name ************/
    const FL_MI_ID = 'id';
    const FL_MI_CODE = 'code';
    const FL_MI_NAME = 'name';
    const FL_MI_QUANTITY = 'quantity';
    const FL_MI_QUALITY = 'quality';
    const FL_MI_TRANSACTION_TYPE = 'transaction_type';
    const FL_MI_TRANSACTION_LIST_ID = 'transaction_list_id';


    /********** Crafted Item Fields Name ************/
    const FL_CI_ID = 'id';
    const FL_CI_CODE = 'code';
    const FL_CI_NAME = 'name';
    const FL_CI_QUANTITY = 'quantity';
    const FL_CI_QUALITY = 'quality';
    const FL_CI_COLOR = 'color';
    const FL_CI_ITEM_CLASS = 'item_class';
    const FL_CI_HP_BONUS = 'hp_bonus';
    const FL_CI_SAP_BONUS = 'sap_bonus';
    const FL_CI_STAM_BONUS = 'stam_bonus';
    const FL_CI_FOCUS_BONUS = 'focus_bonus';
    const FL_CI_HEAL_POINT = 'healpoint';
    const FL_CI_DURABILITY = 'durability';
    const FL_CI_SAPLOAD = 'sapload';
    const FL_CI_CURRENT_SAPLOAD = 'current_sapload';
    const FL_CI_ENCHANT = 'enchant';
    const FL_CI_HIT_RATE = 'hit_rate';
    const FL_CI_RANGE = 'item_range';
    const FL_CI_DESTROYED = 'destroyed';
    const FL_CI_DODGE_MODIFIER = 'dodge_modifier';
    const FL_CI_PARRY_MODIFIER = 'parry_modifier';
    const FL_CI_ADV_DODGE_MODIFIER = 'adv_dodge_modifier';
    const FL_CI_ADV_PARRY_MODIFIER = 'adv_parry_modifier';
    const FL_CI_PROTEC_FACTOR = 'protec_factor';
    const FL_CI_MAX_SLASH_PROTEC = 'max_slash_protec';
    const FL_CI_MAX_PIERCE_PROTEC = 'max_pierce_protec';
    const FL_CI_MAX_BLUNT_PROTEC = 'max_blunt_protec';
    const FL_CI_TRANSACTION_TYPE = 'transaction_type';
    const FL_CI_TRANSACTION_LIST_ID = 'transaction_list_id';


    /********** Global Item Data Access Functions ************/

    /**
     * (PHP 5)<br/>
     * Insert transaction_list in DB
     * @param DateTime $transaction_date
     * @param int $guild_id
     * @return int return new transaction_list database id
     */
    public function insert_transaction_list($transaction_date, $guild_id){
        $sql_cmd = 'INSERT INTO '.$this->get_table_prefix().'_transaction_list ('.item_transaction_DA::FL_TRANS_LIST_DATE.', '.item_transaction_DA::FL_TRANS_LIST_GUILD_ID.') VALUES ('.$this->format_date_to_sql_param($transaction_date).', '.$guild_id.')';

//        echo '<br>item_transaction_DA->insert_transaction_list : '.$sql_cmd;

        $result = $this->execute_sql_command($sql_cmd, true);

        return $result;
    }

    /**
     * (PHP 5)<br/>
     * Get X transaction list from a guild
     * @param int $transaction_list_count count of transaction select
     * @param int $guild_id
     * @return array return null if no result
     */
    public function get_transaction_list($transaction_list_count, $guild_id){
        $trans_list_array = array();

        $sql_cmd = 'SELECT Q.'.item_transaction_DA::FL_TRANS_LIST_ID.', Q.'.item_transaction_DA::FL_TRANS_LIST_DATE.', SUM( Q.count ) as Count 
                    FROM (
                            SELECT tl.'.item_transaction_DA::FL_TRANS_LIST_ID.', tl.'.item_transaction_DA::FL_TRANS_LIST_DATE.', count( * ) AS count
                            FROM '.$this->get_table_prefix().'_transaction_list tl
                            INNER JOIN '.$this->get_table_prefix().'_crafted_item_transaction cit
                            ON cit.'.item_transaction_DA::FL_CI_TRANSACTION_LIST_ID.' = tl.'.item_transaction_DA::FL_TRANS_LIST_ID.'
                            AND tl.'.item_transaction_DA::FL_TRANS_LIST_GUILD_ID.' = '.$guild_id.'
                            GROUP BY tl.'.item_transaction_DA::FL_TRANS_LIST_ID.', tl.'.item_transaction_DA::FL_TRANS_LIST_DATE.'

                            UNION

                            SELECT tl.'.item_transaction_DA::FL_TRANS_LIST_ID.', tl.'.item_transaction_DA::FL_TRANS_LIST_DATE.', count( * ) AS count
                            FROM '.$this->get_table_prefix().'_transaction_list tl
                            INNER JOIN '.$this->get_table_prefix().'_material_item_transaction mit ON mit.'.item_transaction_DA::FL_MI_TRANSACTION_LIST_ID.' = tl.'.item_transaction_DA::FL_TRANS_LIST_ID.'
                            AND tl.'.item_transaction_DA::FL_TRANS_LIST_GUILD_ID.' = '.$guild_id.' 
                            GROUP BY tl.'.item_transaction_DA::FL_TRANS_LIST_ID.', tl.'.item_transaction_DA::FL_TRANS_LIST_DATE.'
                    )Q
                    GROUP BY Q.'.item_transaction_DA::FL_TRANS_LIST_ID.', Q.'.item_transaction_DA::FL_TRANS_LIST_DATE.'
                    ORDER BY Q.'.item_transaction_DA::FL_TRANS_LIST_ID.' DESC 
                    LIMIT '.$transaction_list_count;

//        echo '<br>item_transaction_DA->get_transaction_list : '.$sql_cmd;

        $result = $this->execute_sql_command($sql_cmd, false);

        if (isset($result[0])){
            foreach ($result as $tl){

                $trans_list_array[] = new Transaction_List($tl[item_transaction_DA::FL_TRANS_LIST_ID], $tl[item_transaction_DA::FL_TRANS_LIST_DATE], $tl['Count']);
            }
        }
        
        return (isset($trans_list_array) ? $trans_list_array : null);
    }

    /**
     * (PHP 5)<br/>
     * Get item transaction 
     * @param int $transaction_list_id
     * @param int $transaction_type NULL : all transaction, 0 : deposit, 1 : withdrawal
     * @return array
     */
    public function get_item_transaction($transaction_list_id, $transaction_type){

        $trans_list = array();

        $mi_array = $this->get_material_item_transaction($transaction_list_id, $transaction_type);

        if (isset($mi_array)){
            foreach($mi_array as $mi){
                $trans_list[] = $mi;
            }
        }

        $ci_array = $this->get_crafted_item_transaction($transaction_list_id, $transaction_type);

        if (isset($ci_array)){
            foreach($ci_array as $ci){
                $trans_list[] = $ci;
            }
        }

        return (isset($trans_list) ? $trans_list : null);
    }


    /**
     * (PHP 5)<br/>
     * Insert a Transaction
     * @param Item_Transaction $trans
     */
    public function insert_item_transaction(Item_Transaction $trans){
        if ($trans->get_item()->get_item_type_id() == Enum_Item_Type::CRAFTED_ITEM_TYPE_ID) {
            $this->insert_crafted_item($trans);
        }
        else {
            $this->insert_material_item_transaction($trans);
        }
    }

    /********** Material Item Data Access Functions ************/

    /**
     * (PHP 5)<br/>
     * Get all Transaction on Material_Item
     * @param int $transaction_list_id
     * @param int $transaction_type NULL : all transaction, 0 : deposit, 1 : withdrawal
     * @return array of Transaction
     */
    private function get_material_item_transaction($transaction_list_id, $transaction_type){
        $sql_cmd = 'SELECT * FROM '.$this->get_table_prefix().'_material_item_transaction
                            WHERE '.item_transaction_DA::FL_MI_TRANSACTION_LIST_ID.' = '.$transaction_list_id;

        if (isset($transaction_type)) {
            $sql_cmd .= ' AND '.item_transaction_DA::FL_MI_TRANSACTION_TYPE.' = '.$transaction_type;
        }

        $sql_cmd .= ' ORDER BY '.item_transaction_DA::FL_MI_TRANSACTION_LIST_ID.' DESC, '.item_transaction_DA::FL_MI_TRANSACTION_TYPE.' DESC, '
                            .item_transaction_DA::FL_MI_QUALITY.' DESC';

//        echo '<br>item_transaction_DA->get_material_item_transaction : '.$sql_cmd;

        $item_array = $this->execute_sql_command($sql_cmd, false);

        for ($i = 0; $i < count($item_array); $i++) {
            $item = $item_array[$i];

            $mi = new Material_Item(-1, $item[item_transaction_DA::FL_MI_ID], $item[item_transaction_DA::FL_MI_CODE],
                    $item[item_transaction_DA::FL_MI_NAME], $item[item_transaction_DA::FL_MI_QUALITY], $item[item_transaction_DA::FL_MI_QUANTITY],
                    substr($item[item_transaction_DA::FL_MI_CODE], 9, 3), null);

            $result[] = new Item_Transaction($mi, $item[item_transaction_DA::FL_MI_TRANSACTION_TYPE], $item[item_transaction_DA::FL_MI_TRANSACTION_LIST_ID]);
        }

        return (isset($result) ? $result : null);
    }

    /**
     * (PHP 5)<br/>
     * Insert Transaction on Material_Item
     * @param Item_Transaction $trans
     */
    private function insert_material_item_transaction(Item_Transaction $trans){
        $sql_cmd = 'INSERT INTO '.$this->get_table_prefix().'_material_item_transaction ('.item_transaction_DA::FL_MI_CODE.', '
                    .item_transaction_DA::FL_MI_NAME.', '.item_transaction_DA::FL_MI_QUANTITY.', '.item_transaction_DA::FL_MI_QUALITY.', '
                    .item_transaction_DA::FL_MI_TRANSACTION_TYPE.', '.item_transaction_DA::FL_MI_TRANSACTION_LIST_ID.')
                    VALUES ('.$this->format_string_to_sql_param($trans->get_item()->get_code()).', '
                            .$this->format_string_to_sql_param($trans->get_item()->get_name()).',
                        '.$trans->get_item()->get_quantity().', '.$trans->get_item()->get_quality().', '
                        .$trans->get_transaction_type().', '.$trans->get_transaction_list_id().')';

//        echo '<br>item_transaction_DA->insert_material_item_transaction : '.$sql_cmd;

        $this->execute_sql_command($sql_cmd, true);
    }


    /********** Crafted Item Data Access Functions ************/

    /**
     * (PHP 5)<br/>
     * Get all Transaction on Crafted_Item
     * @param int $transaction_list_id
     * @param int $transaction_type NULL : all transaction, 0 : deposit, 1 : withdrawal
     * @return array of Transaction
     */
    private function get_crafted_item_transaction($transaction_list_id, $transaction_type) {
        $sql_cmd = 'SELECT * FROM '.$this->get_table_prefix().'_crafted_item_transaction WHERE '.item_transaction_DA::FL_CI_TRANSACTION_LIST_ID.' = '.$transaction_list_id;

        if (isset($transaction_type)) {
            $sql_cmd .= ' AND '.item_transaction_DA::FL_CI_TRANSACTION_TYPE.' = '.$transaction_type;
        }

        $sql_cmd .= ' ORDER BY '.item_transaction_DA::FL_CI_TRANSACTION_LIST_ID.' DESC, '.item_transaction_DA::FL_CI_TRANSACTION_TYPE.' DESC, '
                            .item_transaction_DA::FL_CI_QUALITY.' DESC';;

//        echo '<br>item_transaction_DA->get_crafted_item_transaction : '.$sql_cmd;

        $item_array = $this->execute_sql_command($sql_cmd, false);

	for ($i = 0; $i < count($item_array); $i++) {
            $item = $item_array[$i];

            $skin = 1;
            if (strpos($item[item_transaction_DA::FL_CI_CODE],'_2.') == true) $skin = 2;
            if (strpos($item[item_transaction_DA::FL_CI_CODE],'_3.') == true) $skin = 3;

            $ci = new Crafted_Item(-1, $item[item_transaction_DA::FL_CI_ID], $item[item_transaction_DA::FL_CI_CODE], $item[item_transaction_DA::FL_CI_NAME], $item[item_transaction_DA::FL_CI_QUALITY], $item[item_transaction_DA::FL_CI_QUANTITY],
                    $item[item_transaction_DA::FL_CI_ITEM_CLASS],  $skin, $item[item_transaction_DA::FL_CI_COLOR], $item[item_transaction_DA::FL_CI_HEAL_POINT], $item[item_transaction_DA::FL_CI_DURABILITY],
                    $item[item_transaction_DA::FL_CI_SAPLOAD], $item[item_transaction_DA::FL_CI_CURRENT_SAPLOAD], $item[item_transaction_DA::FL_CI_ENCHANT], $item[item_transaction_DA::FL_CI_HIT_RATE], $item[item_transaction_DA::FL_CI_RANGE],
                    $item[item_transaction_DA::FL_CI_DODGE_MODIFIER], $item[item_transaction_DA::FL_CI_PARRY_MODIFIER], $item[item_transaction_DA::FL_CI_ADV_DODGE_MODIFIER], $item[item_transaction_DA::FL_CI_ADV_PARRY_MODIFIER], $item[item_transaction_DA::FL_CI_PROTEC_FACTOR],
                    $item[item_transaction_DA::FL_CI_MAX_SLASH_PROTEC], $item[item_transaction_DA::FL_CI_MAX_PIERCE_PROTEC], $item[item_transaction_DA::FL_CI_MAX_BLUNT_PROTEC], $item[item_transaction_DA::FL_CI_DESTROYED],
                    $item[item_transaction_DA::FL_CI_HP_BONUS], $item[item_transaction_DA::FL_CI_SAP_BONUS], $item[item_transaction_DA::FL_CI_STAM_BONUS], $item[item_transaction_DA::FL_CI_FOCUS_BONUS], null);

             $result[] = new Item_Transaction($ci, $item[item_transaction_DA::FL_CI_TRANSACTION_TYPE], $item[item_transaction_DA::FL_CI_TRANSACTION_LIST_ID]);
        }

        return (isset($result) ? $result : null);
    }

    /**
     * (PHP 5)<br/>
     * Insert Transaction on Crafted_Item
     * @param Item_Transaction $trans
     */
    private function insert_crafted_item(Item_Transaction $trans){

        $sql_cmd = 'INSERT INTO '.$this->get_table_prefix().'_crafted_item_transaction ('.item_transaction_DA::FL_CI_CODE.','.item_transaction_DA::FL_CI_NAME.','.item_transaction_DA::FL_CI_QUALITY.','.item_transaction_DA::FL_CI_QUANTITY.'
                            ,'.item_transaction_DA::FL_CI_ITEM_CLASS.','.item_transaction_DA::FL_CI_COLOR.','.item_transaction_DA::FL_CI_HEAL_POINT.','.item_transaction_DA::FL_CI_DURABILITY.'
                            ,'.item_transaction_DA::FL_CI_SAPLOAD.','.item_transaction_DA::FL_CI_CURRENT_SAPLOAD.','.item_transaction_DA::FL_CI_ENCHANT.','.item_transaction_DA::FL_CI_HIT_RATE.','.item_transaction_DA::FL_CI_RANGE.'
                            ,'.item_transaction_DA::FL_CI_DODGE_MODIFIER.','.item_transaction_DA::FL_CI_PARRY_MODIFIER.','.item_transaction_DA::FL_CI_ADV_DODGE_MODIFIER.','.item_transaction_DA::FL_CI_ADV_PARRY_MODIFIER.','.item_transaction_DA::FL_CI_PROTEC_FACTOR.'
                            ,'.item_transaction_DA::FL_CI_MAX_SLASH_PROTEC.','.item_transaction_DA::FL_CI_MAX_PIERCE_PROTEC.','.item_transaction_DA::FL_CI_MAX_BLUNT_PROTEC.','.item_transaction_DA::FL_CI_DESTROYED.'
                            ,'.item_transaction_DA::FL_CI_HP_BONUS.','.item_transaction_DA::FL_CI_SAP_BONUS.','.item_transaction_DA::FL_CI_STAM_BONUS.','.item_transaction_DA::FL_CI_FOCUS_BONUS.'
                            ,'.item_transaction_DA::FL_CI_TRANSACTION_TYPE.','.item_transaction_DA::FL_CI_TRANSACTION_LIST_ID.')

                        VALUES ('.$this->format_string_to_sql_param($trans->get_item()->get_code()).','.$this->format_string_to_sql_param($trans->get_item()->get_name()).','.$trans->get_item()->get_quality().','.$trans->get_item()->get_quantity().'
                            ,'.$trans->get_item()->get_item_class_id().','.$this->format_null_value($trans->get_item()->get_color()).','.$this->format_null_value($trans->get_item()->get_healpoint()).','.$this->format_null_value($trans->get_item()->get_durability()).'
                            ,'.$this->format_null_value($trans->get_item()->get_sapload()).','.$this->format_null_value($trans->get_item()->get_current_sapload()).','.$this->format_null_value($trans->get_item()->get_enchant()).'
                            ,'.$this->format_null_value($trans->get_item()->get_hit_rate()).','.$this->format_null_value($trans->get_item()->get_range()).'
                            ,'.$this->format_null_value($trans->get_item()->get_dodge_modif()).','.$this->format_null_value($trans->get_item()->get_parry_modif()).','.$this->format_null_value($trans->get_item()->get_adv_dodge_modif()).'
                            ,'.$this->format_null_value($trans->get_item()->get_adv_parry_modif()).','.$this->format_null_value($trans->get_item()->get_prot_factor()).'
                            ,'.$this->format_null_value($trans->get_item()->get_max_slash_prot()).','.$this->format_null_value($trans->get_item()->get_max_pierce_prot()).','.$this->format_null_value($trans->get_item()->get_max_blunt_prot()).'
                            ,'.$this->format_null_value($trans->get_item()->get_destroyed()).','.$this->format_null_value($trans->get_item()->get_healpoint_bonus()).','.$this->format_null_value($trans->get_item()->get_sap_bonus()).'
                            ,'.$this->format_null_value($trans->get_item()->get_stam_bonus()).','.$this->format_null_value($trans->get_item()->get_focus_bonus()).'
                            ,'.$trans->get_transaction_type().','.$trans->get_transaction_list_id().')';

//        echo '<br>item_transaction_DA->insert_crafted_item : '.$sql_cmd;

        $this->execute_sql_command($sql_cmd, true);
    }

}
?>
